﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using RockCompany.Common.Constant;
using System.Reflection;
using System.Collections;

namespace RockCompany.Common.Lib
{
    public class CommonModel
    {
        #region Variables
        private List<object> dataList;
        public List<object> DataList
        {
            get { return dataList; }
            set { dataList = value; }
        }

        protected DBConnector dbConnector;

        protected Translator _translator;
        public Translator translator
        {
            get { return _translator; }
            set { _translator = value; }
        }

        protected Form _owner;
        public Form Owner
        {
            get { return _owner; }
            set { _owner = value; }
        }
        #endregion

        #region Utility methods
        // Check datatable if it contains data ( return True)
        public static bool existData(DataTable dt)
        {
            return dt != null && dt.Rows.Count > 0;
        }

        // Bind data to a combobox
        public static void bindCombo(ComboBox cb, string displayMem, string valueMem, DataTable dt)
        {
            if (existData(dt))
            {
                cb.DisplayMember = displayMem;
                cb.ValueMember = valueMem;
                cb.DataSource = dt;
            }
        }

        // Bind data to a datagridview
        public static void bindDGV(DataGridView dgv, DataTable dt)
        {
            if (existData(dt))
            {
                dgv.DataSource = dt;
            }
        }

        public static List<string> getCheckedList(string colChk, string colID, DataGridView dgv)
        {
            List<string> lsResult = new List<string>();
            foreach (DataGridViewRow dgvRow in dgv.Rows)
            {
                object chkVal = dgvRow.Cells[colChk].EditedFormattedValue;
                if (chkVal != null && chkVal.Equals(true))
                {
                    lsResult.Add(dgvRow.Cells[colID].Value.ToString());
                }
            }
            return lsResult;
        }

        public void addRemoveCheckedList(int checkedCol, string valueCol, DataGridView dgTable)
        {
            bool isChecked = (bool)dgTable.CurrentRow.Cells[checkedCol].EditedFormattedValue;
            if (isChecked)
            {
                DataList.Add(dgTable.CurrentRow);
            }
            else
            {
                if (DataList.Exists(element => element == dgTable.CurrentRow))
                    DataList.Remove(dgTable.CurrentRow);
            }
        }

        public void addRemoveCheckedList(int checkedCol, DataRow row)
        {
            bool isChecked = Util.parseBoolean(row[checkedCol]);
            if (isChecked)
                dataList.Add(row);
            else
            {
                if (dataList.Exists(element => element == row))
                    dataList.Remove(row);
            }
        }

        public void buildDataGrid(DataGridView dg, List<System.Reflection.PropertyInfo> propeties, List<string> readOnlyCols)
        {
            dg.AutoGenerateColumns = false;
            foreach (System.Reflection.PropertyInfo prop in propeties)
            {
                DataGridViewColumn col = null;
                if (prop.PropertyType == typeof(bool))
                    col = new DataGridViewCheckBoxColumn();
                else
                {
                    col = new DataGridViewTextBoxColumn();
                    // Display format of DateTime, Number
                    if (prop.PropertyType == typeof(DateTime))
                    {
                        col.DefaultCellStyle.Format = CommonConstantIF.DATE_FORMAT;
                    }
                    else if (prop.PropertyType == typeof(decimal) || prop.PropertyType == typeof(int))
                    {
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    }
                }
                col.Name = prop.Name;
                if (readOnlyCols != null)
                {
                    col.ReadOnly = readOnlyCols.Contains(prop.Name);
                }
                col.HeaderText = translator.getControlLabel("COL_" + prop.Name);
                col.DataPropertyName = prop.Name;
                if (readOnlyCols != null)
                    col.ReadOnly = readOnlyCols.Contains(prop.Name);
                dg.Columns.Add(col);
            }
        }

        public DataTable buildEmptyDataTable(List<PropertyInfo> properties)
        {
            DataTable emptyDT = new DataTable();
            foreach (PropertyInfo pro in properties)
            {
                emptyDT.Columns.Add(pro.Name, pro.PropertyType);
                // Add a default value
                if (pro.PropertyType == typeof(decimal))
                {
                    emptyDT.Columns[pro.Name].DefaultValue = 0;
                }
            }
            return emptyDT;
        }

        // Copy data from source DT to destination DT with a specific column list that is same in Destination and Source
        // If data is already exists in destination table, skip copying
        public void copyDataTable(DataTable srcDT, DataTable desDT, List<string> columns)
        {
            foreach (DataRow srcRow in srcDT.Rows)
            {
                // check values of source row in destination
                bool exists = false;
                int count = 0;
                foreach (DataRow desRow in desDT.Rows)
                {
                    count = 0;
                    foreach (string col in columns)
                    {
                        if (desRow[col].Equals(srcRow[col]))
                        {
                            count++;
                        }
                    }

                    if (count == columns.Count)
                    {
                        exists = true;
                        break;
                    }
                }

                if (!exists)// || desDT.Rows.Count < 1)
                {
                    DataRow destRow = desDT.NewRow();
                    foreach (string colName in columns)
                    {
                        destRow[colName] = srcRow[colName];
                    }
                    if (desDT.Columns.Contains(CommonConstantIF.CHON))
                    {
                        destRow[CommonConstantIF.CHON] = false;
                    }
                    desDT.Rows.Add(destRow);
                }

            }
        }

        public void copyDataTable2(DataTable srcDT, DataTable desDT, string[] srcCols, string[] desCols)
        {
            foreach (DataRow srcRow in srcDT.Rows)
            {
                // check values of source row in destination
                bool exists = false;
                int count = 0;
                foreach (DataRow desRow in desDT.Rows)
                {
                    count = 0;
                    for (int i = 0; i < srcCols.Length; i++)
                    {
                        if (desRow[desCols[i]].Equals(srcRow[srcCols[i]]))
                        {
                            count++;
                        }
                    }

                    if (count == srcCols.Length)
                    {
                        exists = true;
                        break;
                    }
                }

                if (!exists)
                {
                    DataRow destRow = desDT.NewRow();
                    for (int j = 0; j < srcCols.Length; j++)
                    {
                        destRow[desCols[j]] = srcRow[srcCols[j]];
                    }
                    if (desDT.Columns.Contains(CommonConstantIF.CHON))
                    {
                        destRow[CommonConstantIF.CHON] = false;
                    }
                    desDT.Rows.Add(destRow);
                }

            }
        }

        // If dValue is an invalid double value, add null
        public void addDoubleParameters(Dictionary<string, object> paras, string sKey, double dValue)
        {
            if (dValue.Equals(double.NaN))
            {
                paras.Add(sKey, System.DBNull.Value);
            }
            else
            {
                paras.Add(sKey, dValue);
            }
        }

        // If dValue is an invalid double value, add null
        public void addDecimalParameters(Dictionary<string, object> paras, string sKey, object dValue)
        {
            if (Util.parseDecimal(dValue) == decimal.MinValue) // Invalid decimal
            {
                paras.Add(sKey, System.DBNull.Value);
            }
            else
            {
                paras.Add(sKey, Util.parseDecimal(dValue));
            }
        }
        #endregion

        #region Main Methods
        public bool removeRow(string sTableName, string sWhereClause, Dictionary<string, object> parametrs_values)
        {
            bool res = false;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
                dbConnector.connect();
            if (dbConnector.isConnected())
            {
                dbConnector.removeParameters();
                string sSql = "DELETE " + sTableName;
                sSql += sWhereClause;
                if (parametrs_values != null)
                {
                    foreach (var pair in parametrs_values)
                    {
                        dbConnector.addParameters(pair.Key, pair.Value);
                    }
                }
                res = dbConnector.excuteNonQuery(sSql) > 0;
                dbConnector.removeParameters();
                dbConnector.close();
            }
            return res;
        }

        public DataTable selectData(string sSql)
        {
            DataTable res = null;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
                dbConnector.connect();
            if (dbConnector.isConnected())
            {
                res = dbConnector.selectData(sSql);
                if (res == null)
                    InforDialog.showInforBox(Owner, translator.getMessageCaption(CommonConstantIF.SelectError), translator.getMessageCaption(CommonConstantIF.ErrorBoxCap));
                if (res.Rows.Count == 0)
                    InforDialog.showInforBox(Owner, translator.getMessageCaption(CommonConstantIF.EmptySelect), translator.getMessageCaption(CommonConstantIF.InforBoxCap));

                dbConnector.removeParameters();
                dbConnector.close();
            }
            return res;
        }

        public bool insertData(string sTableName, Dictionary<string, object> parametrs_values)
        {
            int res = 0;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            if (dbConnector.isConnected())
            {
                string sSql = "INSERT INTO " + sTableName + "(";

                foreach (string s in parametrs_values.Keys)
                    sSql += s + ",";
                sSql = sSql.Substring(0, sSql.Length - 1) + ")";

                sSql += " VALUES(";

                if (parametrs_values != null)
                {
                    foreach (var pair in parametrs_values)
                    {
                        sSql += "@" + pair.Key + ",";
                        dbConnector.addParameters(pair.Key, pair.Value);
                    }
                    sSql = sSql.Substring(0, sSql.Length - 1);
                }
                sSql += ")";
                res = dbConnector.excuteNonQuery(sSql);
                parametrs_values.Clear();
                dbConnector.removeParameters();
                dbConnector.close();
            }
            return res > 0;
        }

        public bool updateData(string sTableName, string sSetClause, Dictionary<string, object> parametrs_values, string sClause)
        {
            int res = 0;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            if (dbConnector.isConnected())
            {
                string sSql = "UPDATE " + sTableName;
                sSql += sSetClause;
                sSql += sClause;

                if (parametrs_values != null)
                {
                    foreach (var pair in parametrs_values)
                        dbConnector.addParameters(pair.Key, pair.Value);
                }
                res = dbConnector.excuteNonQuery(sSql);
                parametrs_values.Clear();
                dbConnector.removeParameters();
                dbConnector.close();
            }
            return res > 0;
        }

        public bool updateData2(string sTableName, string whereClause, Dictionary<string, object> parametrs_values)
        {
            int res = 0;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            if (dbConnector.isConnected())
            {
                string sSql = "UPDATE " + sTableName;
                sSql += " SET";

                if (parametrs_values != null)
                {
                    foreach (var pair in parametrs_values)
                    {
                        sSql += " " + pair.Key + " = @" + pair.Key + ",";
                        dbConnector.addParameters(pair.Key, pair.Value);
                    }
                    sSql = sSql.Substring(0, sSql.Length - 1);
                    sSql += " WHERE " + whereClause;
                    res = dbConnector.excuteNonQuery(sSql);
                    parametrs_values.Clear();
                    dbConnector.removeParameters();
                    dbConnector.close();
                }
            }
            return res > 0;
        }

        public DataTable selectWithWhereClause(string tableName, string whereClause, Dictionary<string, object> paras)
        {
            string Sql = "SELECT *" +
                        " FROM " + tableName +
                        " WHERE " + whereClause;
            DataTable res = null;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            dbConnector.removeParameters();
            if (dbConnector.isConnected())
            {
                foreach (string key in paras.Keys)
                {
                    dbConnector.addParameters(key, paras[key]);
                }
                res = dbConnector.selectData(Sql);
            }
            return res;
        }

        public DataTable excuteProcedure(string prosName, Dictionary<string, object> paras)
        {
            DataTable res = null;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            dbConnector.removeParameters();
            if (dbConnector.isConnected())
            {
                foreach (string key in paras.Keys)
                {
                    dbConnector.addParameters(key, paras[key]);
                }
                dbConnector.setProcedureMode();
                res = selectData(prosName);
                dbConnector.setTextMode();
            }
            return res;
        }
        #endregion
    }
}
